package com.cvit.service;

import java.beans.IntrospectionException;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import com.cvit.dao.AchievementMapper;
import com.cvit.model.Achievement;
import com.cvit.model.ExcelBean;
import com.cvit.utils.ExcelUtil;
import com.cvit.utils.Utils;


@Service
public class AchievementService {

	@Autowired
	AchievementMapper achievementMapper;
	
	/**
	 * 查询成绩表单（包含学生表信息）
	 * */
	public List<Achievement> getAchievement() {
		// TODO Auto-generated method stub
		List<Achievement> achievement = achievementMapper.selectAll(null);
		return achievement;
	}

	/**
	 * 根据Id、学生序号、身份证号、考生号查询成绩表单
	 * */
	public Achievement getAchievementBy(Achievement achievement) {
		// TODO Auto-generated method stub
		Achievement achievements = achievementMapper.selectAchievementBy(achievement);
		return achievements;
	}
	
	/**
	 * 根据Id查询 （boolean）
	 * */
	public boolean getAchievementById(String studentId){
		Achievement achievement = achievementMapper.selectByStudentId(studentId);
			return Utils.isNull(achievement);
	}
	
	
	/**
	 * 导入Excel
	 * */
	public void importExcelInfo(InputStream in, MultipartFile file) throws Exception{
	    List<List<Object>> listob = ExcelUtil.getBankListByExcel(in,file.getOriginalFilename()); 
	    //遍历listob数据，把数据放到List中
	    for (int i = 0; i < listob.size(); i++) {
	        List<Object> ob = listob.get(i);
	        Achievement achievement = new Achievement();
	        //设置编号  
	        //通过遍历实现把每一列封装成一个model中，再把所有的model用List集合装载
	        achievement.setId(JudgeIdRepeat());
	        achievement.setStudentId(String.valueOf(ob.get(0)));
	        achievement.setMathe(String.valueOf(ob.get(1)));
	        achievement.setChinese(String.valueOf(ob.get(2)));
	        achievement.setEnglish(String.valueOf(ob.get(3)));
	        achievement.setComprehensive(String.valueOf(ob.get(4)));
	        achievement.setTotal(String.valueOf(ob.get(5)));
	        boolean achievementId = getAchievementById(String.valueOf(ob.get(0)));
	        if(achievementId==false){
	        	achievementMapper.deleteByStudentId(achievement.getStudentId());
	        }
	        	achievementMapper.insert(achievement);
	    }  
	}
	
	/**
	 * 导出Excel
	 * */
	public XSSFWorkbook exportExcelInfo(String achievementData) throws InvocationTargetException, ClassNotFoundException, IntrospectionException, ParseException, IllegalAccessException {  
	    //根据条件查询数据，把数据装载到一个list中  
	    List<Achievement> list = achievementMapper.selectAll(null);
	    List<ExcelBean> excel=new ArrayList<>();
	    Map<Integer,List<ExcelBean>> map=new LinkedHashMap<>();
	    XSSFWorkbook xssfWorkbook=null;
	    //设置标题栏
	    excel.add(new ExcelBean("学生序号","studentId",0));
	    excel.add(new ExcelBean("学生号","eamineeId",0));
	    excel.add(new ExcelBean("身份证号","cardId",0));
	    excel.add(new ExcelBean("姓名","name",0));
	    excel.add(new ExcelBean("数学","mathe",0));
	    excel.add(new ExcelBean("语文","chinese",0));
	    excel.add(new ExcelBean("英语","english",0));
	    excel.add(new ExcelBean("综合素质","comprehensive",0));
	    excel.add(new ExcelBean("合计","total",0));
	    excel.add(new ExcelBean("上传时间","startTime",0));
	    map.put(0, excel);
	    String sheetName = achievementData;
	    //调用ExcelUtil的方法
	    xssfWorkbook = ExcelUtil.createExcelFile(Achievement.class, list, map, sheetName);
	    return xssfWorkbook;
	}
	
	/**
	 * Id判重
	 * */
	public String JudgeIdRepeat(){
		String id=null;
		int i=0;
		for(;i==0;){
			String orderNo = "04";
			SimpleDateFormat sdf=new SimpleDateFormat("yyMMdd");
			String nowTime = sdf.format(new Date())+Utils.get6Radom();
			orderNo+=nowTime;
			id = orderNo;
			if(achievementMapper.selectByPrimaryKey(id)==null){
				i=1;
			}
		}
		return id;
	}

	/**
	 *根据考生号查寻
	 * */
	public Achievement getAchievementByEamineeId(String EamineeId) {
		// TODO Auto-generated method stub
		Achievement achievements = achievementMapper.selectEamineeId(EamineeId);
		return achievements;
	}

}
